package sql

import (
	"database/sql"
	"fmt"
	"reflect"
	"strconv"
	"strings"

	"gitee.com/yxd1990/golang-common/collections"
	"github.com/astaxie/beego/orm"
	"github.com/pkg/errors"
)

type SqlWhereBuilder struct {
	where  []string
	params []interface{}
}

func (this *SqlWhereBuilder) Raw(whereStr string, values ...interface{}) *SqlWhereBuilder {
	this.where = append(this.where, whereStr)
	if len(values) > 0 {
		this.params = append(this.params, values...)
	}
	return this
}

func (this *SqlWhereBuilder) IsWhereEmpty() bool {
	return len(this.where) == 0
}

func NewSqlWhereBuilder() *SqlWhereBuilder {
	rt := &SqlWhereBuilder{}
	rt.Reset()
	return rt
}

func (this *SqlWhereBuilder) Add(field string, value interface{}) *SqlWhereBuilder {
	op := ""
	if strings.Contains(field, "__") {
		ops := strings.Split(field, "__")
		op = ops[1]
		switch op {
		case "gt":
			op = ">"
		case "lt":
			op = "<"
		case "elt":
			op = "<="
		case "eq":
			op = "="
		case "in", "notIn":
			if len(ops) <= 2 {
				if op == "notIn" {
					op = "not in"
				}
				this.where = append(this.where, fmt.Sprintf("%s %s ('%s')", ops[0], op, collections.Join(value, "','")))
				return this
			}
		case "egt":
			op = ">="
		case "neq":
			op = "!="
		case "icontains", "contains", "startWith", "endWidth", "istartWith", "iendWidth":
			if v, ok := value.(string); ok {
				v = strings.ReplaceAll(v, "%", "%%")
				switch op {
				case "icontains", "contains":
					value = "%" + v + "%"
				case "startWith", "istartWith":
					value = v + "%"
				case "endWith", "iendWidth":
					value = "%" + v
				}
			}
			if op[0:1] != "i" {
				op = "like binary"
			} else {
				op = "like"
			}
		}
		field = ops[0]
		if len(ops) > 2 && ops[2] == "exp" {
			this.where = append(this.where, fmt.Sprintf("%s %s %s", field, op, value))
			return this
		}
	} else {
		op = "="
	}
	this.where = append(this.where, fmt.Sprintf("%s %s ?", field, op))
	this.params = append(this.params, value)
	return this
}

//func (this *SqlWhereBuilder)  {
//
//}
func (this *SqlWhereBuilder) Reset() *SqlWhereBuilder {
	this.where = nil
	this.params = nil
	return this
}
func (this *SqlWhereBuilder) WhereString(includesWhere bool) string {
	if len(this.where) > 0 {
		rt := strings.Join(this.where, " and ")
		if includesWhere {
			return "where " + rt
		}
		return rt
	}
	return ""
}
func (this *SqlWhereBuilder) WhereAndParams(includesWhere bool) (string, []interface{}) {
	return this.WhereString(includesWhere), this.params
}

type SqlBuilder struct {
	where  SqlWhereBuilder
	limit  string
	order  string
	fields string
	table  string
	alias  string
	orm    orm.Ormer
}

func NewSqlBuilder(table string) *SqlBuilder {
	rt := &SqlBuilder{}
	rt.Reset(table)
	return rt
}
func NewSqlBuilderWithOrm(table string, o orm.Ormer) *SqlBuilder {
	rt := &SqlBuilder{}
	rt.orm = o
	rt.Reset(table)
	return rt
}

func (this *SqlBuilder) Where(field string, value interface{}) *SqlBuilder {
	this.where.Add(field, value)
	return this
}
func (this *SqlBuilder) RawWhere(whereStr string, params ...interface{}) *SqlBuilder {
	this.where.Raw(whereStr, params...)
	return this
}
func (this *SqlBuilder) Filter(field string, value interface{}) *SqlBuilder {
	this.where.Add(field, value)
	return this
}
func (this *SqlBuilder) RawFilter(whereStr string, params ...interface{}) *SqlBuilder {
	this.where.Raw(whereStr, params...)
	return this
}
func (this *SqlBuilder) Table(table string) *SqlBuilder {
	this.table = table
	return this
}
func (this *SqlBuilder) Alias(alias string) *SqlBuilder {
	this.alias = alias
	return this
}

func (this *SqlBuilder) Limit(offset int, size int) *SqlBuilder {
	this.limit = fmt.Sprintf("limit %d,%d", offset, size)
	return this
}

func (this *SqlBuilder) Page(page, pageSize int) *SqlBuilder {
	this.Limit((page-1)*pageSize, pageSize)
	return this
}

func (this *SqlBuilder) Reset(table string) *SqlBuilder {
	this.where.Reset()
	this.limit = ""
	this.order = ""
	this.fields = "*"
	this.table = table
	this.alias = ""
	return this
}

func (this *SqlBuilder) Order(orderString string) *SqlBuilder {
	this.order = "order by " + orderString
	return this
}

func (this *SqlBuilder) Fields(fields string) *SqlBuilder {
	if fields == "" {
		fields = "*"
	}
	this.fields = fields
	return this
}

func (this *SqlBuilder) String() string {
	return fmt.Sprintf("select %s from %s %s %s %s %s", this.fields, this.table, this.alias, this.WhereString(true), this.order, this.limit)
}

//
//func (this *SqlBuilder)  {
//
//}

func (this *SqlBuilder) UpdateString(params orm.Params) string {
	j := 0
	keys := make([]string, len(params))
	for k := range params {
		keys[j] = fmt.Sprintf("%s=?", k)
		j++
	}
	return fmt.Sprintf("update %s set %s %s", this.table, strings.Join(keys, ","), this.WhereString(true))
}

func (this *SqlBuilder) Update(params orm.Params) (result sql.Result, err error) {
	var keys []string
	var values []interface{}
	for k, v := range params {
		if strings.Contains(k, "__exp") {
			keys = append(keys, fmt.Sprintf("%s=%s", k[:len(k)-5], v))
		} else {
			keys = append(keys, fmt.Sprintf("%s=?", k))
			values = append(values, v)
		}
	}
	if len(values) == 0 {
		err = errors.New("params to update can not be empty")
		return
	}
	if this.where.IsWhereEmpty() {
		err = errors.New("where params cat not be empty")
		return
	}
	result, err = this.orm.Raw(fmt.Sprintf("update %s set %s %s", this.table, strings.Join(keys, ","), this.WhereString(true)), append(values, this.where.params...)...).Exec()
	return
}

func (this *SqlBuilder) Inc(fieldname string, step int) (result sql.Result, err error) {
	if this.where.IsWhereEmpty() {
		err = errors.New("where params cat not be empty")
		return
	}
	str := fmt.Sprintf("update %s set %s=%s+%d %s", this.table, fieldname, fieldname, step, this.WhereString(true))
	result, err = this.orm.Raw(str, this.where.params...).Exec()
	return
}

func (this *SqlBuilder) Dec(fieldname string, step int) (result sql.Result, err error) {
	if this.where.IsWhereEmpty() {
		err = errors.New("where params cat not be empty")
		return
	}
	str := fmt.Sprintf("update %s set %s=%s-%d %s", this.table, fieldname, fieldname, step, this.WhereString(true))
	result, err = this.orm.Raw(str, this.where.params...).Exec()
	return
}

func (this *SqlBuilder) WhereString(includesWhere bool) string {
	return this.where.WhereString(includesWhere)
}

func (this *SqlBuilder) StringAndParams() (string, []interface{}) {
	return this.String(), this.where.params
}

func (this *SqlBuilder) Params() []interface{} {
	return this.where.params
}

func (this *SqlBuilder) CountString() string {
	return fmt.Sprintf("select %s from %s %s %s", "count(*)", this.table, this.alias, this.WhereString(true))
}

func (this *SqlBuilder) QueryCount() (int64, error) {
	if this.orm == nil {
		panic("sqlBuilder orm param can not be empty")
	}
	var list orm.ParamsList
	num, err := this.orm.Raw(this.CountString(), this.where.params...).ValuesFlat(&list)
	if err == nil && num > 0 {
		if rt, ok := list[0].(string); ok {
			v, err := strconv.ParseInt(rt, 10, 64)
			if err == nil {
				return v, nil
			}
		}
	}
	return 0, err
}

func (this *SqlBuilder) QueryRows(containers ...interface{}) error {
	if this.orm == nil {
		panic("sqlBuilder orm param can not be empty")
	}
	_, err := this.orm.Raw(this.String(), this.where.params...).QueryRows(containers...)
	if err != nil {
		return err
	}
	return nil
}

func (this *SqlBuilder) QueryScalar(scalar interface{}) error {
	if this.orm == nil {
		panic("sqlBuilder orm param can not be empty")
	}
	v := reflect.ValueOf(scalar)
	if v.Kind() != reflect.Ptr {
		panic("QueryScalar scalar param must be a pointer")
	}
	plist := orm.ParamsList{}
	_, err := this.orm.Raw(this.String(), this.where.params...).ValuesFlat(&plist)
	if err != nil {
		return err
	}
	if len(plist) > 0 {
		elValue := plist[0]
		v.Elem().Set(reflect.ValueOf(elValue))
	}
	return nil
}

func (this *SqlBuilder) QueryRow(containers ...interface{}) error {
	if this.orm == nil {
		panic("sqlBuilder orm param can not be empty")
	}
	return this.orm.Raw(this.String(), this.where.params...).QueryRow(containers...)
}

func (this *SqlBuilder) Quot(str string, escapePercent bool) string {
	if escapePercent {
		return strings.ReplaceAll(strings.ReplaceAll(str, "%", "%%"), "'", "\\'")
	}
	return strings.ReplaceAll(str, "'", "\\'")
}
